CREATE OR REPLACE FUNCTION transfer_ownership_to_gateuser() RETURNS VOID AS '
-- This function changes the owner of all tables and views owned by the current 
-- user to gateuser.
DECLARE 
  cur CURSOR FOR 
      SELECT ''ALTER TABLE '' || schemaname || ''.'' || quote_ident(tablename) || '' OWNER TO gateuser;'' 
      FROM pg_tables WHERE schemaname=''public'' and tableowner=user
    UNION
    SELECT ''ALTER TABLE '' || schemaname || ''.'' || quote_ident(viewname) || '' OWNER TO gateuser;'' 
    FROM pg_views WHERE schemaname=''public'' and viewowner=user;
  vAlterStmt  CHARACTER VARYING;

  cur2 CURSOR FOR 
      select ''ALTER FUNCTION '' || p.proname || ''('', CAST(p.proargtypes AS OID[]), p.pronargs from pg_proc p, pg_user u where u.usesysid = p.proowner and u.usename = user;
  
  counter int8;
  vNArgs smallint;
  arrayvar oid[];
  vTypeId oid;
  vTypeName  CHARACTER VARYING;
BEGIN
    OPEN cur;
    LOOP
        FETCH NEXT FROM cur INTO vAlterStmt;
        IF NOT FOUND THEN
            EXIT;
        END IF;
        EXECUTE vAlterStmt;
    END LOOP;
    CLOSE cur;

    OPEN cur2;
    LOOP
        FETCH NEXT FROM cur2 INTO vAlterStmt, arrayvar, vNArgs;
        IF NOT FOUND THEN
            EXIT;
        END IF;
        
        IF vNArgs > 0 THEN

            counter = array_lower(arrayvar, 1);

            SELECT INTO vTypeId  "?column?"[counter] FROM (SELECT arrayvar) AS foo ;

            SELECT replace(MAX(typname), ''_'', '''') INTO vTypeName FROM pg_type WHERE typelem = vTypeId;

            vAlterStmt = vAlterStmt || vTypeName;
            counter = counter + 1;

            WHILE (counter <= array_upper(arrayvar, 1)) LOOP
            SELECT INTO vTypeId  "?column?"[counter] FROM (SELECT arrayvar) AS foo ;

            SELECT replace(MAX(typname), ''_'', '''') INTO vTypeName FROM pg_type WHERE typelem = vTypeId;

            vAlterStmt = vAlterStmt || '', '' || vTypeName;

            counter = counter + 1;
            END LOOP;

        END IF;
        vAlterStmt = vAlterStmt || '') OWNER TO gateuser;'';

        BEGIN
            RAISE NOTICE ''%s'', vAlterStmt;
            EXECUTE vAlterStmt;
        EXCEPTION
            WHEN undefined_function THEN
            -- DO NOTHING
        END;
    END LOOP;
    CLOSE cur2;
    


END;
' LANGUAGE 'plpgsql';

-- SELECT transfer_ownership_to_gateuser();